﻿Imports System.Data
Imports System.Xml
Imports System.Data.SqlClient
Imports System.Web.Mail
Imports System.Text
Imports Microsoft.Office.Core

Public Class frmRetailSalesmenPerformanceReport
    Dim dset, ds As New DataSet
    Dim util As New AppsUtility.EncDec
    Public strCon As String = util.toDecrypt(System.Configuration.ConfigurationSettings.AppSettings("SQLString")).Replace("serps", CurrentDB)

    Private Sub dgformat1(ByVal RowCount As Integer)
        Dim grdTableStyle As New DataGridTableStyle
        With grdTableStyle
            .AlternatingBackColor = Color.AliceBlue
            .HeaderFont = New Font("Tahoma", 8.0!, FontStyle.Bold)
            .MappingName = "r1" ''PrODucts
            .PreferredColumnWidth = 250
            .HeaderBackColor = Color.LightSlateGray
            .HeaderForeColor = Color.LightCyan
            .PreferredRowHeight = 15
            .RowHeadersVisible = False
            .RowHeaderWidth = 0
        End With
        'stapleSales) stapleSales, sum(staplex.imageSales) imageSales, sum(staplex.exciteSales) exciteSales, " & _
        '"sum(staplex.rangeSales) rangeSales, sum(staplex.hotSales) hotSales from

        Dim cs1 As DataGridColumnStyle
        cs1 = New DataGridTextBoxColumn
        cs1.Width = 190
        cs1.MappingName = "username"
        cs1.HeaderText = "Sales Person"
        cs1.NullText = ""
        cs1.ReadOnly = True
        grdTableStyle.GridColumnStyles.Add(cs1)
        '
        cs1 = New DataGridTextBoxColumn
        cs1.Width = 60
        cs1.MappingName = "salesman"
        cs1.HeaderText = "User ID"
        cs1.NullText = ""
        cs1.ReadOnly = True
        cs1.Alignment = HorizontalAlignment.Left
        grdTableStyle.GridColumnStyles.Add(cs1)
        ''
        '
        cs1 = New DataGridTextBoxColumn
        cs1.Width = 100
        cs1.MappingName = "DepartmentName"
        cs1.HeaderText = "Showroom"
        cs1.NullText = ""
        cs1.ReadOnly = True
        cs1.Alignment = HorizontalAlignment.Left
        grdTableStyle.GridColumnStyles.Add(cs1)
        '
        '
        cs1 = New DataGridTextBoxColumn
        cs1.Width = 100
        cs1.MappingName = "Amount"
        cs1.HeaderText = "Total Sales"
        cs1.NullText = ""
        cs1.ReadOnly = True
        cs1.Alignment = HorizontalAlignment.Right
        grdTableStyle.GridColumnStyles.Add(cs1)
        ''
        '
        '
        cs1 = New DataGridTextBoxColumn
        cs1.Width = 100
        cs1.MappingName = "invoices"
        cs1.HeaderText = "Total Invoices"
        cs1.NullText = ""
        cs1.ReadOnly = True
        cs1.Alignment = HorizontalAlignment.Right
        grdTableStyle.GridColumnStyles.Add(cs1)
        ''
        '
        cs1 = New DataGridTextBoxColumn
        cs1.Width = 100
        cs1.MappingName = "InvoiceAverage"
        cs1.HeaderText = "Invoice Average"
        cs1.NullText = ""
        cs1.ReadOnly = True
        cs1.Alignment = HorizontalAlignment.Right
        grdTableStyle.GridColumnStyles.Add(cs1)
        ''
        'cs1 = New DataGridTextBoxColumn
        'cs1.Width = 100
        'cs1.MappingName = "Sales Parti %"
        'cs1.HeaderText = "Sales Parti %"
        'cs1.NullText = ""
        'cs1.ReadOnly = True
        'cs1.Alignment = HorizontalAlignment.Right
        'grdTableStyle.GridColumnStyles.Add(cs1)
        ''
        '
        dgProduct.TableStyles.Add(grdTableStyle)
    End Sub

    Private Sub Rtl_SalesmanPerformanceReport_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim ICdata As New ICdataLogic
        Dim ds As DataSet
        ds = ICdata.GetCompanyShowrooms(3)
        cmbLocation.DataSource = ds.Tables(0)
        cmbLocation.ValueMember = "DepartmentID"
        cmbLocation.DisplayMember = "DepartmentName"
        cmbLocation.SelectedValue = 0
    End Sub



    Private Sub cmdRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRefresh.Click

        Me.Cursor = Cursors.WaitCursor
        Dim dateFrom, dateto As Date
        Try
            dateFrom = Date.Parse(dtpAge.Text)
            dateto = Date.Parse(DateTimePicker1.Text)
        Catch ex As Exception
            Err.Raise(666, "GetStockStatusReport", "Invalid Date")
            Exit Sub
        End Try
        Dim sFdate As String = dateFrom.ToString("MM/dd/yyyy")
        sFdate = sFdate & " 00:00"
        Dim sEdate As String = dateto.ToString("MM/dd/yyyy")
        sEdate = sEdate & " 23:59"

        Dim locparam, rtnlocparam As String

        If cmbLocation.SelectedValue = 0 Then
            locparam = ""
            rtnlocparam = ""
        Else
            locparam = " and invshowroomid=" & cmbLocation.SelectedValue
            rtnlocparam = " and rtnshowroomid=" & cmbLocation.SelectedValue
        End If

        Dim sqlCon As SqlConnection = New SqlConnection(strCon)
        Dim strsql As String

        strsql = "select X.* from " & _
        "( " & _
        "select u.username,salesman,DepartmentName,sum(invnet) Amount,count(invshowroomid) invoices " & _
        "from invhead,userprofile u,Department " & _
        "where (salesman<>'' or salesman <> '0') and u.Usercode = salesman and DepartmentID = invshowroomid " & locparam & " " & _
        " and invdatetime between '" & sFdate & "' and '" & sEdate & "' and InvStatus='POST' " & _
        "group by DepartmentName,salesman,username " & _
        ") as X "

        sqlCon.Open()
        Dim da As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Try
            da.SelectCommand.CommandTimeout = 1800
            da.Fill(dset, "r1")
        Catch ex As SqlException
            MsgBox("##" & ex.ToString())
        End Try
        'MessageBox.Show(dset.Tables("r1").Rows.Count)
        strsql = "select Sum(G.sales) sales from (" & _
                 "select (invnet) sales from invhead where invdatetime between '" & sFdate & "' and '" & sEdate & _
                 "' and InvStatus='POST'" & locparam & " " & _
                 "union all " & _
                 "select (0-rtnnet) sales from rtnhead where rtndatetime between '" & sFdate & "' and '" & sEdate & "'" & rtnlocparam & " " & _
                 ") as G "
        Dim tots As Decimal
        'MessageBox.Show(strsql)
        Dim da1 As New SqlClient.SqlDataAdapter(strsql, sqlCon)
        Try
            da1.SelectCommand.CommandTimeout = 1800
            da1.Fill(ds, "r1")
            'MessageBox.Show()  
        Catch ex As SqlException
            MsgBox("##" & ex.ToString())
        End Try
        'MessageBox.Show(ds.Tables("r1").Rows.Count)
        'tots = CDbl(da1.Tables("r1").Rows(0)(0))

        Dim dRow As DataRow
        Dim salespart, salestot, invtot As Decimal
        dset.Tables("r1").Columns.Add("Sales Parti %")
        dset.Tables("r1").Columns.Add("InvoiceAverage")
        Try
            For Each dRow In ds.Tables("r1").Rows
                tots = CDbl(dRow("sales")).ToString("#0.000")
                Exit For
            Next

            For Each dRow In dset.Tables("r1").Rows
                dRow("InvoiceAverage") = CDbl(dRow("Amount")) / CDbl(dRow("invoices"))
                dRow("InvoiceAverage") = CDbl(dRow("InvoiceAverage")).ToString("#0.000")
                dRow("Sales Parti %") = ((CDbl(dRow("Amount")) / tots) * 100).ToString("#0.00")
                salespart += CDbl(dRow("Sales Parti %"))
                salestot += CDbl(dRow("Amount"))
                invtot += CDbl(dRow("invoices"))
            Next
            salestot = Decimal.Round(salestot, 2)
            invtot = Decimal.Round(invtot, 2)


        Catch ex As Exception
            MsgBox("Date Range Should be in Current Financial year", 64, "SERPS")
            'Me.Hide()
        End Try






        dRow = dset.Tables("r1").NewRow()
        dRow("username") = "Attended Sales : "
        dRow("Amount") = salestot.ToString("#0.000")
        dRow("invoices") = invtot
        If invtot = 0 Then
            dRow("InvoiceAverage") = 0
        Else
            dRow("InvoiceAverage") = Decimal.Round((salestot / invtot), 3)

        End If

        'dRow("InvoiceAverage") = (CDbl(salestot) / CDbl(invtot)).ToString("#0.000")

        dset.Tables("r1").Rows.Add(dRow)

        dRow = dset.Tables("r1").NewRow()
        dRow("username") = "Unattended Sales : "
        dRow("Amount") = CDbl(CDbl(tots) - CDbl(salestot)).ToString("#0.000")
        dset.Tables("r1").Rows.Add(dRow)


        dRow = dset.Tables("r1").NewRow()
        dRow("username") = "Total : "
        dRow("Amount") = CDbl(tots).ToString("#0.000")
        dset.Tables("r1").Rows.Add(dRow)

        dgProduct.DataSource = dset.Tables("r1")
        With dgProduct
            '.BackgroundColor = GridBackgroundColor
            .TableStyles.Clear()
            .CaptionBackColor = System.Drawing.Color.SlateBlue
            .CaptionForeColor = System.Drawing.Color.LemonChiffon
            .AllowSorting = True
            .AlternatingBackColor = System.Drawing.Color.AliceBlue
            .ParentRowsBackColor = System.Drawing.Color.Lavender
            .ParentRowsForeColor = System.Drawing.Color.SlateBlue
            .HeaderBackColor = System.Drawing.Color.LemonChiffon
            .AllowNavigation = False
            .ParentRowsVisible = False
            .RowHeadersVisible = False

        End With
        Dim cm As CurrencyManager = BindingContext(dgProduct.DataSource, dgProduct.DataMember)
        Dim dv As DataView = cm.List
        dv.AllowNew = False
        dgformat1(dset.Tables("r1").Rows.Count)
        ' ICdata = Nothing
        Me.Cursor = Cursors.Default
        dset.Tables.Clear()
        dset.Clear()
        ds.Tables.Clear()
        ds.Clear()
        sqlCon.Close()

        Me.Cursor = Cursors.Default
    End Sub

    Private Sub cmdPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPrint.Click
        Try

            rtlmsg = "From " & dtpAge.Text & " To " & DateTimePicker1.Text & " , " & cmbLocation.Text
            'Dim ds As DataSet = New DataSet("SerpsRpt")
            Dim table As DataTable = New DataTable("SerpsRpt")
            table.Columns.Add("Str1")
            table.Columns.Add("Str2")
            table.Columns.Add("Str3")
            table.Columns.Add("Str4")
            table.Columns.Add("Str5")
            table.Columns.Add("Str6")
            'table.Columns.Add("Str7")


            'table.Columns.Add("Int1")
            'table.Columns.Add("Dec1")
            'table.Columns.Add("Dec2")
            'table.Columns.Add("Str7")


            Dim mrow, drow As Integer
            Dim cm As CurrencyManager = BindingContext(dgProduct.DataSource, dgProduct.DataMember)
            Dim dv As DataView = cm.List
            dv.RowStateFilter = DataViewRowState.CurrentRows
            mrow = dv.Count
            Dim n As Integer
            For n = 0 To mrow - 1
                Dim nrow As DataRow = table.NewRow()
                nrow("Str1") = dgProduct.Item(n, 0)
                nrow("Str2") = dgProduct.Item(n, 1)
                nrow("Str3") = dgProduct.Item(n, 2)
                nrow("Str4") = dgProduct.Item(n, 3)
                nrow("Str5") = dgProduct.Item(n, 4)
                nrow("Str6") = dgProduct.Item(n, 5)
                'nrow("Str7") = dgProduct.Item(n, 6)

                table.Rows.Add(nrow)
            Next
            dspo.Tables.Add(table)


            Dim ViewReport As New frmRetailSalesmenPerformRpt
            ViewReport.ShowDialog()

        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        Finally
            dspo.Clear()
            dspo.Tables.Clear()
            'rtlmsg
        End Try
    End Sub

    Private Sub cmdExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExit.Click
        Me.Hide()
    End Sub

  
End Class